package com.lq.utils.jdbc;

import org.apache.commons.collections.map.ListOrderedMap;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 行列匹配（行转列）
 * @author Administrator
 *
 */
public class RowColumnMatching {
	
	public static void main(String[] args) {
//		org.apache.log4j.PropertyConfigurator.configure(System.getProperty("user.dir")+"/config/log4j.properties");
//		com.lq.util.jdbc.PropertyConfigurator.configure("config/lqjdbc.properties");
//		List list=aaa(
//			"select measuring_point_addr from record group by measuring_point_addr", 
//			"select DATE_FORMAT(date,'%Y-%m-%d %H:%i') as date from record group by DATE_FORMAT(date,'%Y-%m-%d %H:%i') order by DATE_FORMAT(date,'%Y-%m-%d %H:%i') desc",
//"""
//		
//select 
//	DATE_FORMAT(date,'%Y-%m-%d %H:%i') as date,
//	measuring_point_addr,
//	temperature 
//from record 
//where 
//DATE_FORMAT(date,'%Y-%m-%d %H:%i')>=?
//and DATE_FORMAT(date,'%Y-%m-%d %H:%i')<=?
//group by DATE_FORMAT(date,'%Y-%m-%d %H:%i') 
//order by DATE_FORMAT(date,'%Y-%m-%d %H:%i') desc
//
//""", 
//			1, 
//			5, 
//			935229L, 
//			"date",
//			"measuring_point_addr",
//			"temperature"
//		);
//		System.out.println(JSONArray.toJSONString(list));
	}

	/**
	 * 
	 * @param colSql 查询列
	 * @param rowSql 查询行
	 * @param sql 查询结果集跟行列匹配
	 * @param pageNumber
	 * @param pageSize
	 * @param totalCount 
	 * @param rowFieldName 第一列
	 * @param colFieldName 标题
	 * @param cellFieldName 显示字段
	 * @return
	 */
	public static List<ListOrderedMap> aaa(
			String colSql,
			String rowSql,
			String sql,
			Integer pageNumber,
			Integer pageSize,
			Long totalCount,
			String rowFieldName,
			String colFieldName,
			String cellFieldName
	) {
		List<ListOrderedMap> newList=new ArrayList();
		ListOrderedMap map=new ListOrderedMap();//表头
		
		ListOrderedMap rowMap=new ListOrderedMap();
		ListOrderedMap colMap=new ListOrderedMap();
		
		//开始表头的渲染
		List<ListOrderedMap> colList=Jdbc.find(colSql);
		for(int i=0;i<colList.size();i++) {
			map.put(colList.get(i).get(colList.get(i).get(0)), i);
			System.out.println(colList.get(i).get(colList.get(i).get(0)));
		}
		newList.add(map);
		//开始渲染第一列
		List<ListOrderedMap> rowList=Jdbc.findPage(rowSql, pageNumber, pageSize, totalCount,new Object[] {}).getData();
		for(int i=0;i<rowList.size();i++) {
			ListOrderedMap mapCol1=new ListOrderedMap();
			mapCol1.put(rowFieldName, rowList.get(i).get(rowFieldName));
			newList.add(mapCol1);
		}
		
		//查询出结果集
		//Page p=Jdbc.findPage(sql, pageNumber, pageSize, totalCount,new Object[] {});
		List data=Jdbc.find(sql,new Object[]{newList.get(newList.size()-1).get(rowFieldName),newList.get(1).get(rowFieldName)});
		//开始每格的渲染
		for(int i=1;i<newList.size();i++) {//行
			ListOrderedMap mapRowTem=newList.get(i);
			String Ai=mapRowTem.get(rowFieldName).toString();
			for(int j=0;j<colList.size();j++) {//列
				String Ji=colList.get(j).getValue(0).toString();
				//定义一个状态，单元格为Null时填充
				boolean isNull=true;
				for(int k=0;k<data.size();k++) {
					//用列名和行名匹配对应数据
					Map map2 = (Map) data.get(k);
					if(map2.get(rowFieldName).toString().equals(Ai) && map2.get(colFieldName).equals(Ji)) {
						//开始填充此单元格
						mapRowTem.put(Ji, map2.get(cellFieldName));
						isNull=false;
						break;
					}
				}
				if(isNull) {
					mapRowTem.put(Ji, "");
				}
			}
		}
		return newList;
	}
	
}
